﻿CREATE PROCEDURE [dbo].[proc_Order_Getlist_NoFinish_In]
	(
		@InCompanyId varchar(500),
		@StateId int,
		@TypeId int,
		@WayId int,
		@ProcessId int,
		@Stext nvarchar(50),
		@StartDate nvarchar(10),
		@EndDate nvarchar(10),
		@StartIndex int,
		@EndIndex int,
		@NOE_Flag int
	)
AS
begin
Declare @cSql varchar(4000)

Set @cSql = 'CompanyId In('+@InCompanyId+')'

If @StateId<>0
	Set @cSql = @cSql + ' And StateId='+Convert(varchar(10),@StateId)
Else
	Set @cSql = @cSql + ' And StateId<>5'

If @TypeId<>0
	Set @cSql = @cSql + ' And TypeId='+Convert(varchar(10),@TypeId)

If @WayId<>0
	Set @cSql = @cSql + ' And WayId='+Convert(varchar(10),@WayId)

If @ProcessId<>0
	Set @cSql = @cSql + ' And ProcessId='+Convert(varchar(10),@ProcessId)

If @Stext<>''
	Set @cSql = @cSql + ' And (CusName like ''%'+@Stext+'%'' Or Salesman like ''%'+@Stext+'%'')'

If @NOE_Flag<>100
	Set @cSql = @cSql + ' And NOE_Flag=' + Convert(varchar(10),@NOE_Flag)
Else
	Set @cSql = @cSql + ' And NOE_Flag<>1'

Set @cSql='Declare @sRq Datetime,@eRq Datetime
Set @sRq = Convert(Datetime,''' + @StartDate + ' 00:00:00'')
Set @eRq = Convert(Datetime,''' + @EndDate + ' 23:59:59'')
;WITH list As(Select ROW_NUMBER() OVER (ORDER BY TradeDate DESC,Id DESC)AS Row
		,Id
		,OId
		,isnull(OrderId,0) as OrderId
		,CusId
		,CusName
		,CusPerson
		,CusPersonTel
		,CusDescription
		,CusEstimate
		,TypeId
		,TypeName
		,WayId
		,WayName
		,ProcessId
		,ProcessName
		,Salesman
		,SalesmanAccount
		,TradeDate
		,CreateDate
		,isnull(YingShouPrice,0) as YingShouPrice
		,isnull(ShiShouPrice,0) as ShiShouPrice
		,Discount
		,PaymentTypeId
		,PaymentType
		,BankId
		,Bank
		,PaymentDescription
		,OtherDescription
		,DepId
		,CompanyId
		,StateId
		,IsGreen
		,NOE_Flag
		,IsNULL((Select Title From Order_State Where Id=[Order].StateId),''...'') As StateName
		--,Isnull((select Pizhu from Customer_PiZhu where CustomerId=CusId),'''') as Pizhu --批注内容字段 2015.08.20 
		--,Isnull((select CusState from Customer_PiZhu where CustomerId=CusId),0) as CusState --是否有批注字段 
		From [Order]
		Where '+@cSql + '
		And CreateDate>=@sRq And CreateDate<=@eRq
	)
	
	Select *,(Select Count(0) From list) As RecordCount From list Where Row Between '+Convert(varchar(10),@StartIndex)+' and '+Convert(varchar(10),@EndIndex)+' Order By Row'
	Exec(@cSql)
End
